import * as XLSX from 'xlsx';
import * as fs from 'fs';
import * as path from 'path';

/**
 * Excel生成器 - 自适应模板生成机制
 * 根据后端错误报告自动修正Excel格式
 */
export class ExcelGenerator {
  private testTimestamp: string;
  private currentTemplate: any;
  private outputDir: string;

  constructor(testTimestamp: string) {
    this.testTimestamp = testTimestamp;
    this.outputDir = path.join(process.cwd(), 'test-results', 'excel-files');
    this.ensureOutputDir();
    this.initializeTemplate();
  }

  private ensureOutputDir(): void {
    if (!fs.existsSync(this.outputDir)) {
      fs.mkdirSync(this.outputDir, { recursive: true });
    }
  }

  /**
   * 初始化基础模板 - V2三表结构（严格按照schema）
   */
  private initializeTemplate(): void {
    // V2导入使用三表结构：Dishes, Variants, Options
    // 严格按照 /backend/src/constants/importV2Schema.js 的定义
    this.currentTemplate = {
      Dishes: [
        {
          dish_sku: `AUTOTEST_${this.testTimestamp}_DISH1`,
          dish_slug: `test-dish-autotest-${this.testTimestamp}-1`,
          name_zh: `测试菜品_AUTOTEST_${this.testTimestamp}`,
          name_en: `Test_Dish_AUTOTEST_${this.testTimestamp}`,
          category: '主食',
          base_status: 'active',
          description_zh: '自动化测试菜品描述',
          description_en: 'Automated test dish description',
          ingredients_zh: '牛肉,生菜,面包',
          ingredients_en: 'Beef, Lettuce, Bread',
          tags: 'test,automated',
          popularity: 50
        },
        {
          dish_sku: `AUTOTEST_${this.testTimestamp}_DISH2`,
          dish_slug: `test-drink-autotest-${this.testTimestamp}-2`,
          name_zh: `测试饮品_AUTOTEST_${this.testTimestamp}`,
          name_en: `Test_Drink_AUTOTEST_${this.testTimestamp}`,
          category: '饮品',
          base_status: 'active',
          description_zh: '自动化测试饮品描述',
          description_en: 'Automated test drink description',
          ingredients_zh: '水,糖,香精',
          ingredients_en: 'Water, Sugar, Flavor',
          tags: 'drink,cold',
          popularity: 80
        }
      ],
      Variants: [
        {
          dish_sku: `AUTOTEST_${this.testTimestamp}_DISH1`,
          name_zh: '标准',
          variant_code: 'STD-001',
          variant_name: 'Standard',
          price: '15.99',
          compare_at_price: '',
          is_default: 'true',
          stock: '100'
        },
        {
          dish_sku: `AUTOTEST_${this.testTimestamp}_DISH2`,
          name_zh: '中杯',
          variant_code: 'MED-001',
          variant_name: 'Medium',
          price: '8.99',
          compare_at_price: '',
          is_default: 'true',
          stock: '50'
        }
      ],
      Options: [
        {
          dish_sku: `AUTOTEST_${this.testTimestamp}_DISH1`,
          name_zh: '配菜选择',
          group_code: 'SIDE',
          group_name: '配菜',
          required: 'false',
          value_code: 'FRIES',
          value_name: '薯条',
          price_delta: '2.00'
        },
        {
          dish_sku: `AUTOTEST_${this.testTimestamp}_DISH2`,
          name_zh: '温度选择',
          group_code: 'TEMP',
          group_name: '温度',
          required: 'true',
          value_code: 'ICE',
          value_name: '冰',
          price_delta: '0'
        }
      ]
    };
  }

  /**
   * 根据错误信息更新模板
   */
  async updateFromErrors(errors: string[]): Promise<void> {
    console.log('根据错误信息更新Excel模板:', errors);
    
    for (const error of errors) {
      await this.processError(error);
    }
  }

  /**
   * 处理单个错误
   */
  private async processError(error: string): Promise<void> {
    const errorLower = error.toLowerCase();
    
    // 处理缺失列错误
    if (errorLower.includes('缺少') || errorLower.includes('missing') || errorLower.includes('required')) {
      await this.handleMissingColumn(error);
    }
    
    // 处理数据类型错误
    if (errorLower.includes('类型') || errorLower.includes('type') || errorLower.includes('format')) {
      await this.handleTypeError(error);
    }
    
    // 处理枚举值错误
    if (errorLower.includes('枚举') || errorLower.includes('enum') || errorLower.includes('allowed values')) {
      await this.handleEnumError(error);
    }
  }

  /**
   * 处理缺失列错误
   */
  private async handleMissingColumn(error: string): Promise<void> {
    // 从错误信息中提取列名
    const columnMatches = error.match(/["'`]([^"'`]+)["'`]|([A-Za-z_][A-Za-z0-9_]*)/g);
    
    if (columnMatches) {
      for (const match of columnMatches) {
        const columnName = match.replace(/["'`]/g, '');
        
        // 添加缺失的列
        if (!this.hasColumn(columnName)) {
          this.addColumn(columnName);
        }
      }
    }
  }

  /**
   * 处理数据类型错误
   */
  private async handleTypeError(error: string): Promise<void> {
    // 根据错误信息调整数据类型
    if (error.includes('数字') || error.includes('number')) {
      this.convertToNumber(error);
    } else if (error.includes('布尔') || error.includes('boolean')) {
      this.convertToBoolean(error);
    } else if (error.includes('字符串') || error.includes('string')) {
      this.convertToString(error);
    }
  }

  /**
   * 处理枚举值错误
   */
  private async handleEnumError(error: string): Promise<void> {
    // 从错误信息中提取允许的枚举值
    const enumMatches = error.match(/\[(.*?)\]/);
    if (enumMatches && enumMatches[1]) {
      const allowedValues = enumMatches[1].split(',').map(v => v.trim().replace(/["']/g, ''));
      
      // 使用第一个允许的值
      if (allowedValues.length > 0) {
        this.updateEnumValue(error, allowedValues[0]);
      }
    }
  }

  /**
   * 检查是否有指定列
   */
  private hasColumn(columnName: string): boolean {
    // 检查三个表中是否有指定列
    const allKeys = [
      ...Object.keys(this.currentTemplate.Dishes[0] || {}),
      ...Object.keys(this.currentTemplate.Variants[0] || {}),
      ...Object.keys(this.currentTemplate.Options[0] || {})
    ];
    return allKeys.some(key => key.toLowerCase() === columnName.toLowerCase());
  }

  /**
   * 添加新列
   */
  private addColumn(columnName: string): void {
    const defaultValue = this.getDefaultValueForColumn(columnName);
    
    // 根据列名判断应该添加到哪个表
    if (columnName.includes('dish_sku') || columnName.includes('name_') || columnName.includes('category')) {
      // 添加到Dishes表
      for (const row of this.currentTemplate.Dishes) {
        row[columnName] = defaultValue;
      }
    } else if (columnName.includes('variant_')) {
      // 添加到Variants表
      for (const row of this.currentTemplate.Variants) {
        row[columnName] = defaultValue;
      }
    } else if (columnName.includes('option_')) {
      // 添加到Options表
      for (const row of this.currentTemplate.Options) {
        row[columnName] = defaultValue;
      }
    } else {
      // 默认添加到Dishes表
      for (const row of this.currentTemplate.Dishes) {
        row[columnName] = defaultValue;
      }
    }
  }

  /**
   * 获取列的默认值
   */
  private getDefaultValueForColumn(columnName: string): any {
    const nameLower = columnName.toLowerCase();
    
    if (nameLower.includes('price') || nameLower.includes('价格')) {
      return 10.00;
    } else if (nameLower.includes('available') || nameLower.includes('可用')) {
      return true;
    } else if (nameLower.includes('featured') || nameLower.includes('推荐')) {
      return false;
    } else if (nameLower.includes('time') || nameLower.includes('时间')) {
      return 10;
    } else if (nameLower.includes('calories') || nameLower.includes('卡路里')) {
      return 200;
    } else if (nameLower.includes('order') || nameLower.includes('排序')) {
      return 1;
    } else if (nameLower.includes('category') || nameLower.includes('分类')) {
      return '主食';
    } else {
      return `默认值_${columnName}`;
    }
  }

  /**
   * 转换为数字类型
   */
  private convertToNumber(error: string): void {
    // 对三个表都进行数字转换
    const tables = [this.currentTemplate.Dishes, this.currentTemplate.Variants, this.currentTemplate.Options];
    
    for (const table of tables) {
      for (const row of table) {
        for (const [key, value] of Object.entries(row)) {
          if (typeof value === 'string' && !isNaN(Number(value))) {
            row[key] = Number(value);
          }
        }
      }
    }
  }

  /**
   * 转换为布尔类型
   */
  private convertToBoolean(error: string): void {
    const tables = [this.currentTemplate.Dishes, this.currentTemplate.Variants, this.currentTemplate.Options];
    
    for (const table of tables) {
      for (const row of table) {
        for (const [key, value] of Object.entries(row)) {
          if (typeof value === 'string') {
            const valueLower = value.toLowerCase();
            if (['true', '是', 'yes', '1'].includes(valueLower)) {
              row[key] = true;
            } else if (['false', '否', 'no', '0'].includes(valueLower)) {
              row[key] = false;
            }
          }
        }
      }
    }
  }

  /**
   * 转换为字符串类型
   */
  private convertToString(error: string): void {
    const tables = [this.currentTemplate.Dishes, this.currentTemplate.Variants, this.currentTemplate.Options];
    
    for (const table of tables) {
      for (const row of table) {
        for (const [key, value] of Object.entries(row)) {
          if (typeof value !== 'string') {
            row[key] = String(value);
          }
        }
      }
    }
  }

  /**
   * 更新枚举值
   */
  private updateEnumValue(error: string, allowedValue: string): void {
    const tables = [this.currentTemplate.Dishes, this.currentTemplate.Variants, this.currentTemplate.Options];
    
    // 根据错误信息找到需要更新的字段
    for (const table of tables) {
      for (const row of table) {
        for (const [key, value] of Object.entries(row)) {
          // 简单的匹配逻辑，可以根据需要改进
          if (error.includes(key) || error.includes(String(value))) {
            row[key] = allowedValue;
          }
        }
      }
    }
  }

  /**
   * 生成测试Excel文件 - V2三表结构
   */
  async generateTestExcel(attempt: number): Promise<string> {
    const filename = `test-excel-v2-attempt-${attempt}-${this.testTimestamp}.xlsx`;
    const filepath = path.join(this.outputDir, filename);
    
    // 创建工作簿
    const workbook = XLSX.utils.book_new();
    
    // 创建三个工作表
    const dishesSheet = XLSX.utils.json_to_sheet(this.currentTemplate.Dishes);
    const variantsSheet = XLSX.utils.json_to_sheet(this.currentTemplate.Variants);
    const optionsSheet = XLSX.utils.json_to_sheet(this.currentTemplate.Options);
    
    // 添加工作表到工作簿
    XLSX.utils.book_append_sheet(workbook, dishesSheet, 'Dishes');
    XLSX.utils.book_append_sheet(workbook, variantsSheet, 'Variants');
    XLSX.utils.book_append_sheet(workbook, optionsSheet, 'Options');
    
    // 写入文件
    XLSX.writeFile(workbook, filepath);
    
    console.log(`生成V2 Excel文件: ${filepath}`);
    return filepath;
  }

  /**
   * 获取当前模板数据
   */
  getCurrentTemplate(): any {
    return JSON.parse(JSON.stringify(this.currentTemplate));
  }

  /**
   * 设置模板数据
   */
  setTemplate(template: any): void {
    this.currentTemplate = JSON.parse(JSON.stringify(template));
  }
}